package com.ruoyi.system.service.impl;

import com.ruoyi.common.core.annotation.Excel;
import com.ruoyi.common.core.utils.CryptoUtils;
import com.ruoyi.common.core.utils.Global;
import com.ruoyi.common.core.utils.StringUtils;
import com.ruoyi.common.core.utils.poi.ExcelUtil;
import com.ruoyi.common.security.utils.SecurityUtils;
import com.ruoyi.system.api.domain.SysDept;
import com.ruoyi.system.api.domain.SysUser;
import com.ruoyi.system.domain.SysDict;
import com.ruoyi.system.domain.TPeopleType;
import com.ruoyi.system.domain.excle.DownloadExcleReqDTO;
import com.ruoyi.system.domain.excle.GongLvCheExcel;
import com.ruoyi.system.domain.excle.GuoMinTiZhiChengRen;
import com.ruoyi.system.domain.excle.GuoMinTiZhiLaoRen;
import com.ruoyi.system.domain.excle.GuoMinTiZhiYouEr;
import com.ruoyi.system.domain.excle.NoDataTipDTO;
import com.ruoyi.system.domain.excle.QuestionnaireDataDTO;
import com.ruoyi.system.domain.excle.TiChengFenExcel;
import com.ruoyi.system.domain.excle.XueYaJiExcel;
import com.ruoyi.system.mapper.SysDeptMapper;
import com.ruoyi.system.mapper.SysDictMapper;
import com.ruoyi.system.mapper.TPeopleTypeMapper;
import com.ruoyi.system.mapper.uploaddownload.UploadMapper;
import com.ruoyi.system.service.DownloadDataService;
import com.ruoyi.system.service.DownloadQuestionService;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

@Service
public class DownloadDataServiceImpl implements DownloadDataService {

    @Autowired
    private SysDictMapper dictMapper;
    @Autowired
    private SysDeptMapper deptMapper;
    @Autowired
    private TPeopleTypeMapper peopleTypeMapper;
    @Autowired
    private UploadMapper downloadMapper;
    @Autowired
    private DownloadQuestionService downloadQuestionService;


    private static final Logger log = LoggerFactory.getLogger(DownloadDataServiceImpl.class);

    // HTML标签正则表达式
    private static final String REGEX_HTML = "<[^>]+>";
    private static final Pattern P_HTML = Pattern.compile(REGEX_HTML, Pattern.CASE_INSENSITIVE);

    // 固定表头定义
    private static final String[] HEAD0 = {"省份", "身份证号", "姓名", "性别", "出生日期", "测试年龄", "城乡工作种类", "所属机构", "机构代码", "测试日期"};
    private static final String[] HEAD_YOUER = {"身高", "身高得分", "身高评价", "身高筛查", "坐高", "坐高筛查", "体重", "体重筛查", "体重指数", "体重指数得分", "体重指数评价",
            "胸围", "胸围筛查", "安静心率", "安静心率筛查", "体脂率", "体脂率筛查", "握力", "握力得分", "握力评价", "握力筛查", "立定跳远", "立定跳远得分",
            "立定跳远评价", "立定跳远筛查", "坐位体前屈", "坐位体前屈得分", "坐位体前屈评价", "坐位体前屈筛查",
            "双脚连续跳", "双脚连续跳得分", "双脚连续跳评价", "双脚连续跳筛查", "15米绕障碍跑", "15米绕障碍跑得分", "15米绕障碍跑评价", "15米绕障碍跑筛查",
            "平衡木", "是否挪步", "平衡木得分", "平衡木评价", "平衡木筛查", "身高/坐高筛查", "身高与体重筛查", "身高与坐高筛查",
            "综合得分", "综合评价", "综合筛查", "收缩压", "舒张压", "心率", "去脂体重的实测值（千克）", "体脂肪量的实测值（千克）", "身体肌肉量的实测值（千克）",
            "左上肢肌肉量的实测值（千克）", "右上肢肌肉量的实测值（千克）", "躯干肌肉量的实测值（千克）", "左下肢肌肉量的实测值（千克）", "右下肢肌肉量的实测值（千克）",
            "躯干脂肪量的实测值（千克）", "脂肪率结果的FAT/W（%）", "gps", "问卷创建日期"};
    private static final String[] HEAD_CHENGREN = {"身高", "身高筛查", "体重", "体重筛查", "体重指数", "体重指数得分", "体重指数评价", "腰围", "腰围筛查",
            "臀围", "臀围筛查", "体脂率", "体脂率得分", "体脂率评价", "体脂率筛查", "肺活量", "肺活量得分", "肺活量评价", "肺活量筛查",
            "功率车", "功率车得分", "功率车评价", "功率车筛查", "握力", "握力得分", "握力评价", "握力筛查", "纵跳", "纵跳得分", "纵跳评价", "纵跳筛查",
            "俯卧撑/跪卧撑", "俯卧撑/跪卧撑得分", "俯卧撑/跪卧撑评价", "俯卧撑/跪卧撑筛查", "1分钟仰卧起坐", "1分钟仰卧起坐得分", "1分钟仰卧起坐评价", "1分钟仰卧起坐筛查",
            "坐位体前屈", "坐位体前屈得分", "坐位体前屈评价", "坐位体前屈筛查", "闭眼单脚站立", "闭眼单脚站立得分", "闭眼单脚站立评价", "闭眼单脚站立筛查",
            "选择反应时", "选择反应时得分", "选择反应时评价", "选择反应时筛查", "背力", "背力筛查", "腰臀比筛查", "脉压差筛查", "身高与体重筛查", "身高与肺活量筛查",
            "身高与握力筛查", "身高与背力筛查", "体重与肺活量筛查", "体重与握力筛查", "体重与背力筛查", "腰围/身高与体重筛查", "臀围/身高与体重筛查",
            "综合得分", "综合评价", "综合筛查", "心率", "安静脉搏筛查", "收缩压", "收缩压筛查", "舒张压", "舒张压筛查", "MET", "绝对值", "相对值", "功率车心率",
            "去脂体重的实测值（千克）", "体脂肪量的实测值（千克）", "身体肌肉量的实测值（千克）", "左上肢肌肉量的实测值（千克）", "右上肢肌肉量的实测值（千克）",
            "躯干肌肉量的实测值（千克）", "左下肢肌肉量的实测值（千克）", "右下肢肌肉量的实测值（千克）", "躯干脂肪量的实测值（千克）", "脂肪率结果的FAT/W（%）", "gps", "问卷创建日期"};
    private static final String[] HEAD_LAOREN = {"身高", "身高筛查", "体重", "体重筛查", "体重指数", "体重指数得分", "体重指数评价", "腰围", "腰围筛查",
            "臀围", "臀围筛查", "体脂率", "体脂率得分", "体脂率评价", "体脂率筛查", "肺活量", "肺活量得分", "肺活量评价", "肺活量筛查",
            "2分钟原地高抬腿_左腿", "2分钟原地高抬腿_右腿", "2分钟原地高抬腿", "2分钟原地高抬腿得分", "2分钟原地高抬腿评价", "2分钟原地高抬腿筛查",
            "握力", "握力得分", "握力评价", "握力筛查", "坐位体前屈", "坐位体前屈得分", "坐位体前屈评价", "坐位体前屈筛查", "闭眼单脚站立", "闭眼单脚站立得分",
            "闭眼单脚站立评价", "闭眼单脚站立筛查", "选择反应时", "选择反应时得分", "选择反应时评价", "选择反应时筛查", "30秒坐站", "30秒坐站得分", "30秒坐站评价", "30秒坐站筛查",
            "腰臀比筛查", "脉压差筛查", "身高与体重筛查", "身高与肺活量筛查", "身高与握力筛查", "体重与肺活量筛查", "体重与握力筛查", "腰围/身高与体重筛查",
            "臀围/身高与体重筛查", "综合得分", "综合评价", "综合筛查", "心率", "安静脉搏筛查", "收缩压", "收缩压筛查", "舒张压", "舒张压筛查",
            "去脂体重的实测值（千克）", "体脂肪量的实测值（千克）", "身体肌肉量的实测值（千克）", "左上肢肌肉量的实测值（千克）", "右上肢肌肉量的实测值（千克）",
            "躯干肌肉量的实测值（千克）", "左下肢肌肉量的实测值（千克）", "右下肢肌肉量的实测值（千克）", "躯干脂肪量的实测值（千克）", "脂肪率结果的FAT/W（%）", "gps", "问卷创建日期"};
    private static final String[] HEAD1 = {"工作单位", "民族", "职称", "职业", "行业", "文化程度", "家庭住址", "手机号"};

    @Override
    public void downloadExcelbydeptid(HttpServletRequest request, HttpServletResponse response, DownloadExcleReqDTO reqDTO) {
        Long deptid = reqDTO.getDeptid();
        String project = reqDTO.getProject();
        String starttime = reqDTO.getStarttime();
        String endtime = reqDTO.getEndtime();

        log.info("导出参数：deptid={}, project={}, starttime={}, endtime={}", deptid, project, starttime, endtime);

        try {
            String organizationCode = "";
            if (deptid == null) {
                SysUser user = SecurityUtils.getLoginUser().getSysUser();
                SysDept org = deptMapper.selectDeptById(user.getDeptId());
                organizationCode = org.getOrganizationcode();
            } else {
                SysDept org = deptMapper.selectDeptById(deptid);
                organizationCode = org.getOrganizationcode();
            }

            // 根据日期查询数据表标识符
            SysDict dictYear = dictMapper.selectDictByLabel(starttime.substring(0, 4));

            if (null != dictYear) {
                String projectTableName = Global.DATATABLEPRE + "_" + dictYear.getValue() + Global.TESTWHOLEDATANAME;
                String huiyuanxinxiTableName = Global.DATATABLEPRE + "_" + dictYear.getValue() + Global.TESTWHOLEDATANAME;

                // 设置响应头
                String filename = "体质测试数据.xlsx";
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.setCharacterEncoding("utf-8");
                response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));

                switch (project) {
                    case "":
                        exportWholeData(response, organizationCode, starttime, endtime, dictYear);
                        break;
                    case Global.GUOMINTIZHICESHINAME:
                        exportGuoMinTiZhiData(response, organizationCode, projectTableName, huiyuanxinxiTableName, starttime, endtime);
                        break;
                    case Global.XUEYAJINAME:
                        exportXueYaJiData(response, organizationCode, projectTableName, huiyuanxinxiTableName, starttime, endtime);
                        break;
                    case Global.GONGLVCHENAME:
                        exportGongLvCheData(response, organizationCode, projectTableName, huiyuanxinxiTableName, starttime, endtime);
                        break;
                    case Global.TICHENGFENNAME:
                        exportTiChengFenData(response, organizationCode, projectTableName, huiyuanxinxiTableName, starttime, endtime);
                        break;
                    case "question":
                        // 这里需要修改downloadQuestionService使其也使用临时文件方式
                        downloadQuestionService.downloadExcel(request, response, organizationCode, starttime, endtime);
                        break;
                }
            } else {
                // 没有数据表，导出提示信息
                exportNoDataTip(response, starttime);
            }

            log.info("文件导出成功");
        } catch (Exception e) {
            log.error("导出数据失败", e);
            // 错误处理逻辑
        }
    }

    // 导出整体数据
    // 改造后的整体数据导出方法
    private void exportWholeData(HttpServletResponse response, String organizationCode,
                                 String starttime, String endtime, SysDict dictYear) throws IOException {
        String projectTableName = Global.DATATABLEPRE + "_" + dictYear.getValue() + Global.TESTWHOLEDATANAME;

        // 创建Excel工作簿（支持多sheet）
        Workbook workbook = new XSSFWorkbook();

        // 查出所有人群类型（幼儿，成年人，老年人）
        List<TPeopleType> peopleTypeList = peopleTypeMapper.getAllList();

        for (TPeopleType peopleType : peopleTypeList) {
            // 根据peopleType的年龄范围确定问卷
            List<Map<String, Object>> questionList = downloadMapper.getQuestion("question", "1",
                    peopleType.getPeopleagemin(), peopleType.getPeopleagemax());

            if (questionList.isEmpty()) {
                continue;
            }

            // 生成问卷ID字符串
            StringBuilder questionId = new StringBuilder();
            for (Map<String, Object> map : questionList) {
                questionId.append(map.get("questionId").toString()).append(",");
            }
            questionId = new StringBuilder(questionId.substring(0, questionId.length() - 1));

            // 根据问卷id查询该问卷包含的问题
            List<Map<String, Object>> paperList = downloadMapper.getPaper(questionId.toString());
            List<String> paperIdStringList = new ArrayList<>();
            for (Map<String, Object> stringObjectMap : paperList) {
                paperIdStringList.add(stringObjectMap.get("paperId").toString());
            }

            // 查询数据
            Map<String, Object> paramMap = new HashMap<>();
            paramMap.put("questionId", questionId.toString());
            paramMap.put("paperIdStringList", paperIdStringList);
            paramMap.put("organizationCode", organizationCode);
            paramMap.put("projectTableName", projectTableName);
            paramMap.put("starttime", starttime);
            paramMap.put("endtime", endtime);
            paramMap.put("peopleAgeMin", peopleType.getPeopleagemin());
            paramMap.put("peopleAgeMax", peopleType.getPeopleagemax());

            List<Map<String, Object>> dataList = downloadMapper.getDownloadWhole(paramMap);

            // 转换为若依框架可用的DTO列表
            List<QuestionnaireDataDTO> dtoList = convertToQuestionnaireDTO(dataList, paperList, peopleType.getPeopletypename());

            // 创建工作表
            String sheetName = "国民体质" + peopleType.getPeopletypename() + "测试数据";
            Sheet sheet = workbook.createSheet(sheetName);

            // 使用自定义方法导出Excel到工作表
            exportDataToSheet(sheet, dtoList, QuestionnaireDataDTO.class);
        }

        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=体质测试数据.xlsx");

        // 写入响应流
        try (OutputStream out = response.getOutputStream()) {
            workbook.write(out);
        } finally {
            workbook.close();
        }
    }
  /*  // 使用POI导出多sheet的Excel
    private void exportWithMultipleSheets(HttpServletResponse response,
                                          List<?> dataList,
                                          String sheetName,
                                          Class<?> clazz) throws IOException {

        Workbook workbook = new XSSFWorkbook();

        // 创建sheet
        Sheet sheet = workbook.createSheet(sheetName);

        // 创建表头
        Row headerRow = sheet.createRow(0);
        List<Field> excelFields = getExcelFields(clazz);

        for (int i = 0; i < excelFields.size(); i++) {
            Field field = excelFields.get(i);
            Excel excelAnnotation = field.getAnnotation(Excel.class);
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(excelAnnotation.name());

            // 设置列宽
            sheet.setColumnWidth(i, (int) (excelAnnotation.width() * 256));
        }

        // 填充数据
        for (int i = 0; i < dataList.size(); i++) {
            Object data = dataList.get(i);
            Row dataRow = sheet.createRow(i + 1);

            for (int j = 0; j < excelFields.size(); j++) {
                Field field = excelFields.get(j);
                field.setAccessible(true);

                try {
                    Object value = field.get(data);
                    Cell cell = dataRow.createCell(j);

                    if (value != null) {
                        cell.setCellValue(value.toString());
                    } else {
                        cell.setCellValue("");
                    }
                } catch (IllegalAccessException e) {
                    log.error("获取字段值失败", e);
                    Cell cell = dataRow.createCell(j);
                    cell.setCellValue("");
                }
            }
        }

        // 写入响应流
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=体质测试数据.xlsx");

        try (OutputStream out = response.getOutputStream()) {
            workbook.write(out);
        } finally {
            workbook.close();
        }
    }*/
    // 改造后的国民体质测试数据导出方法
    private void exportGuoMinTiZhiData(HttpServletResponse response, String organizationCode,
                                       String projectTableName, String huiyuanxinxiTableName,
                                       String starttime, String endtime) throws IOException {
        // 创建Excel工作簿（支持多sheet）
        Workbook workbook = new XSSFWorkbook();

        // 查出所有人群类型（幼儿，成年人，老年人）
        List<TPeopleType> peopleTypeList = peopleTypeMapper.getAllList();

        for (TPeopleType peopleType : peopleTypeList) {
            String sheetName = "国民体质" + peopleType.getPeopletypename() + "测试数据";

            if (peopleType.getId()==1) {
                // 查询数据
                List<GuoMinTiZhiYouEr> dataList = downloadMapper.getDownloadDataYouEr(
                        organizationCode, projectTableName, huiyuanxinxiTableName,
                        starttime, endtime, peopleType.getPeopleagemin(), peopleType.getPeopleagemax());

                // 创建工作表
                Sheet sheet = workbook.createSheet(sheetName);

                // 使用自定义方法导出Excel到工作表
                exportDataToSheet(sheet, dataList, GuoMinTiZhiYouEr.class);

            } else if (peopleType.getId()==2) {
                // 查询数据
                List<GuoMinTiZhiChengRen> dataList = downloadMapper.getDownloadDataChengRen(
                        organizationCode, projectTableName, huiyuanxinxiTableName,
                        starttime, endtime, peopleType.getPeopleagemin(), peopleType.getPeopleagemax());

                // 创建工作表
                Sheet sheet = workbook.createSheet(sheetName);

                // 使用自定义方法导出Excel到工作表
                exportDataToSheet(sheet, dataList, GuoMinTiZhiChengRen.class);

            } else if (peopleType.getId()==3) {
                // 查询数据
                List<GuoMinTiZhiLaoRen> dataList = downloadMapper.getDownloadDataLaoRen(
                        organizationCode, projectTableName, huiyuanxinxiTableName,
                        starttime, endtime, peopleType.getPeopleagemin(), peopleType.getPeopleagemax());

                // 创建工作表
                Sheet sheet = workbook.createSheet(sheetName);

                // 使用自定义方法导出Excel到工作表
                exportDataToSheet(sheet, dataList, GuoMinTiZhiLaoRen.class);
            }
        }

        // 设置响应头
        String filename = "国民体质测试数据.xlsx";
        String encodedFilename = URLEncoder.encode(filename, "UTF-8").replaceAll("\\+", "%20");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=" + encodedFilename);

        // 写入响应流
        try (OutputStream out = response.getOutputStream()) {
            workbook.write(out);
        } finally {
            workbook.close();
        }
    }

    // 导出血压计数据
    private void exportXueYaJiData(HttpServletResponse response,String organizationCode,
                                   String projectTableName, String huiyuanxinxiTableName,
                                   String starttime, String endtime) throws IOException {
        int pageSize = 10000;
        int currentPage = 1;
        boolean hasMoreData = true;
        List<XueYaJiExcel> allData = new ArrayList<>();

        // 分页查询所有数据
        while (hasMoreData) {
            int start = (currentPage - 1) * pageSize;
            List<XueYaJiExcel> pageData = downloadMapper.getDownloadDataXueYaJi(
                    organizationCode, projectTableName, huiyuanxinxiTableName,
                    starttime, endtime, start, pageSize);

            if (pageData.size() < pageSize) {
                hasMoreData = false;
            }

            allData.addAll(pageData);
            currentPage++;
        }



        ExcelUtil<XueYaJiExcel> util = new ExcelUtil<>(XueYaJiExcel.class);
        util.exportExcel(response, allData, "血压计测试数据.xlsx");

    }

    // 导出功率车数据
    private void exportGongLvCheData(HttpServletResponse response, String organizationCode,
                                     String projectTableName, String huiyuanxinxiTableName,
                                     String starttime, String endtime) throws IOException {
        int pageSize = 10000;
        int currentPage = 1;
        boolean hasMoreData = true;
        List<GongLvCheExcel> allData = new ArrayList<>();

        while (hasMoreData) {
            int start = (currentPage - 1) * pageSize;
            List<GongLvCheExcel> pageData = downloadMapper.getDownloadDataGongLvChe(
                    organizationCode, projectTableName, huiyuanxinxiTableName,
                    starttime, endtime, start, pageSize);

            if (pageData.size() < pageSize) {
                hasMoreData = false;
            }

            allData.addAll(pageData);
            currentPage++;
        }
        ExcelUtil<GongLvCheExcel> util = new ExcelUtil<>(GongLvCheExcel.class);
        util.exportExcel(response, allData, "功率车测试数据.xlsx");
    }

    // 导出体成分数据
    private void exportTiChengFenData(HttpServletResponse response, String organizationCode,
                                      String projectTableName, String huiyuanxinxiTableName,
                                      String starttime, String endtime) throws IOException {
        int pageSize = 10000;
        int currentPage = 1;
        boolean hasMoreData = true;
        List<TiChengFenExcel> allData = new ArrayList<>();

        while (hasMoreData) {
            int start = (currentPage - 1) * pageSize;
            List<TiChengFenExcel> pageData = downloadMapper.getDownloadDataTiChengFen(
                    organizationCode, projectTableName, huiyuanxinxiTableName,
                    starttime, endtime, start, pageSize);

            if (pageData.size() < pageSize) {
                hasMoreData = false;
            }

            allData.addAll(pageData);
            currentPage++;
        }
        ExcelUtil<TiChengFenExcel> util = new ExcelUtil<>(TiChengFenExcel.class);
        util.exportExcel(response, allData, "体脂率测试数据.xlsx");
    }

    // 改造后的无数据提示导出方法
    private void exportNoDataTip(HttpServletResponse response, String year) throws IOException {
        List<NoDataTipDTO> tipList = new ArrayList<>();
        NoDataTipDTO tip = new NoDataTipDTO();
        tip.setMessage(year + "年度尚未创建数据表，无法导出数据");
        tipList.add(tip);

        // 使用若依框架的ExcelUtil导出
        ExcelUtil<NoDataTipDTO> util = new ExcelUtil<>(NoDataTipDTO.class);
        util.exportExcel(response, tipList, "提示");
    }



    // 改造后的自定义方法：导出数据到Excel工作表
    private <T> void exportDataToSheet(Sheet sheet, List<T> dataList, Class<T> clazz) throws IOException {
        // 创建表头行
        Row headerRow = sheet.createRow(0);

        // 获取所有带有@Excel注解的字段
        List<Field> excelFields = getExcelFields(clazz);

        // 创建表头
        for (int i = 0; i < excelFields.size(); i++) {
            Field field = excelFields.get(i);
            Excel excelAnnotation = field.getAnnotation(Excel.class);
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(excelAnnotation.name());

            // 设置列宽
            sheet.setColumnWidth(i, (int) (excelAnnotation.width() * 256));
        }

        // 填充数据
        for (int i = 0; i < dataList.size(); i++) {
            T data = dataList.get(i);
            Row dataRow = sheet.createRow(i + 1);

            for (int j = 0; j < excelFields.size(); j++) {
                Field field = excelFields.get(j);
                field.setAccessible(true);

                try {
                    Object value = field.get(data);
                    Cell cell = dataRow.createCell(j);

                    if (value != null) {
                        cell.setCellValue(value.toString());
                    } else {
                        cell.setCellValue("");
                    }
                } catch (IllegalAccessException e) {
                    log.error("获取字段值失败", e);
                    Cell cell = dataRow.createCell(j);
                    cell.setCellValue("");
                }
            }
        }

        // 自动调整列宽
        for (int i = 0; i < excelFields.size(); i++) {
            sheet.autoSizeColumn(i);
        }
    }

    // 获取所有带有@Excel注解的字段
    private <T> List<Field> getExcelFields(Class<T> clazz) {
        List<Field> excelFields = new ArrayList<>();
        Field[] fields = clazz.getDeclaredFields();

        for (Field field : fields) {
            if (field.isAnnotationPresent(Excel.class)) {
                excelFields.add(field);
            }
        }

        // 按orderNum排序
        excelFields.sort((f1, f2) -> {
            Excel excel1 = f1.getAnnotation(Excel.class);
            Excel excel2 = f2.getAnnotation(Excel.class);
            return Integer.compare(excel1.sort(), excel2.sort());
        });

        return excelFields;
    }

    // 数据转换方法 - 将Map数据转换为问卷DTO
    private List<QuestionnaireDataDTO> convertToQuestionnaireDTO(List<Map<String, Object>> dataList,
                                                                 List<Map<String, Object>> paperList,
                                                                 String peopleTypeName) {
        List<QuestionnaireDataDTO> result = new ArrayList<>();

        for (Map<String, Object> data : dataList) {
            QuestionnaireDataDTO dto = new QuestionnaireDataDTO();

            // 设置基本字段
            try {
                // 使用反射设置字段值
                setBasicFields(dto, data);

                // 根据人群类型设置特定字段
                switch (peopleTypeName) {
                    case "幼儿":
                        setYouErFields(dto, data);
                        break;
                    case "成年人":
                        setChengRenFields(dto, data);
                        break;
                    case "老年人":
                        setLaoRenFields(dto, data);
                        break;
                }

                // 设置通用字段
                setCommonFields(dto, data);

                // 设置问卷问题字段
                setQuestionnaireFields(dto, data, paperList);

                // 设置个人信息字段
                setPersonalInfoFields(dto, data);

            } catch (Exception e) {
                log.warn("设置字段失败", e);
            }

            result.add(dto);
        }

        return result;
    }

    // 设置基本字段
    private void setBasicFields(QuestionnaireDataDTO dto, Map<String, Object> data) throws Exception {
        dto.setSheng(getStringValue(data, "sheng"));
        dto.setIdcard(CryptoUtils.decryptSymmetrically(Global.COLS_AES_SECRETKEY, null,
                getStringValue(data, "idcard"), CryptoUtils.Algorithm.Encryption.AES_ECB_PKCS5));
        dto.setRealname(CryptoUtils.decryptSymmetrically(Global.COLS_AES_SECRETKEY, null,
                getStringValue(data, "realname"), CryptoUtils.Algorithm.Encryption.AES_ECB_PKCS5));
        dto.setSex(getStringValue(data, "sex"));
        dto.setBirthdate(getStringValue(data, "birthdate"));
        dto.setTestage(getStringValue(data, "testage"));
        dto.setTowncountry(getStringValue(data, "towncountry"));
        dto.setMechanismname(getStringValue(data, "mechanismname"));
        dto.setMechanismcode(getStringValue(data, "mechanismcode"));
        dto.setTesttime(getStringValue(data, "testtime"));
    }

    // 设置幼儿特定字段
    private void setYouErFields(QuestionnaireDataDTO dto, Map<String, Object> data) {
        String itemsispass = getStringValue(data, "itemsispass");

        dto.setShengaoval(getStringValue(data, "shengaoval"));
        dto.setShengaoscore(getStringValue(data, "shengaoscore"));
        dto.setShengaograde(getStringValue(data, "shengaograde"));
        dto.setShengaoscreening(getScreeningResult(itemsispass, "身高、"));

        dto.setZuogaoval(getStringValue(data, "zuogaoval"));
        dto.setZuogaoscreening(getScreeningResult(itemsispass, "坐高、"));

        dto.setTizhongval(getStringValue(data, "tizhongval"));
        dto.setTizhongscreening(getScreeningResult(itemsispass, "体重、"));

        dto.setTzzsval(getStringValue(data, "tzzsval"));
        dto.setTzzsscore(getStringValue(data, "tzzsscore"));
        dto.setTzzsgrade(getStringValue(data, "tzzsgrade"));

        dto.setXiongweival(getStringValue(data, "xiongweival"));
        dto.setXiongweiscreening(getScreeningResult(itemsispass, "围度_胸围、"));

        dto.setAjxlval(getStringValue(data, "ajxlval"));
        dto.setAjxlscreening(getScreeningResult(itemsispass, "安静心率、"));

        dto.setTzlval(getStringValue(data, "tzlval"));
        dto.setTzlscore(getStringValue(data, "tzlscore"));
        dto.setTzlgrade(getStringValue(data, "tzlgrade"));
        dto.setTzlscreening(getScreeningResult(itemsispass, "体脂率、"));

        dto.setWolival(getStringValue(data, "wolival"));
        dto.setWoliscore(getStringValue(data, "woliscore"));
        dto.setWoligrade(getStringValue(data, "woligrade"));
        dto.setWoliscreening(getScreeningResult(itemsispass, "握力、"));

        dto.setLdtyval(getStringValue(data, "ldtyval"));
        dto.setLdtyscore(getStringValue(data, "ldtyscore"));
        dto.setLdtygrade(getStringValue(data, "ldtygrade"));
        dto.setLdtyscreening(getScreeningResult(itemsispass, "立定跳远、"));

        dto.setZwtqqval(getStringValue(data, "zwtqqval"));
        dto.setZwtqqscore(getStringValue(data, "zwtqqscore"));
        dto.setZwtqqgrade(getStringValue(data, "zwtqqgrade"));
        dto.setZwtqqscreening(getScreeningResult(itemsispass, "坐位体前屈、"));

        dto.setSjlxtval(getStringValue(data, "sjlxtval"));
        dto.setSjlxtscore(getStringValue(data, "sjlxtscore"));
        dto.setSjlxtgrade(getStringValue(data, "sjlxtgrade"));
        dto.setSjlxtscreening(getScreeningResult(itemsispass, "双脚连续跳、"));

        dto.setSwmrzapval(getStringValue(data, "swmrzapval"));
        dto.setSwmrzapscore(getStringValue(data, "swmrzapscore"));
        dto.setSwmrzapgrade(getStringValue(data, "swmrzapgrade"));
        dto.setSwmrzapscreening(getScreeningResult(itemsispass, "15米绕障碍跑、"));

        dto.setZphmval(getStringValue(data, "zphmval"));
        dto.setZphmnb(getStringValue(data, "zphmnb"));
        dto.setZphmscore(getStringValue(data, "zphmscore"));
        dto.setZphmgrade(getStringValue(data, "zphmgrade"));
        dto.setZphmscreening(getScreeningResult(itemsispass, "平衡木、"));

        dto.setSgzgscreening(getScreeningResult(itemsispass, "身高/坐高"));
        dto.setSgtzscreening(getScreeningResult(itemsispass, "身高与体重"));
        dto.setSgzgscreening2(getScreeningResult(itemsispass, "身高与坐高"));

        dto.setZongheScore(getStringValue(data, "zongheScore"));
        dto.setZongheGrade(getStringValue(data, "zongheGrade"));

        String ispassscreening = getStringValue(data, "ispassscreening");
        dto.setIspassscreening(ispassscreening.isEmpty() ? "" : "0".equals(ispassscreening) ? "不通过" : "通过");

        dto.setDiastolicpressure(getStringValue(data, "diastolicpressure"));
        dto.setSystolicpressure(getStringValue(data, "systolicpressure"));
        dto.setHeartrate(getStringValue(data, "heartrate"));
    }

    // 设置成年人特定字段
    private void setChengRenFields(QuestionnaireDataDTO dto, Map<String, Object> data) {
        String itemsispass = getStringValue(data, "itemsispass");

        dto.setShengaoval(getStringValue(data, "shengaoval"));
        dto.setShengaoscreening(getScreeningResult(itemsispass, "身高、"));

        dto.setTizhongval(getStringValue(data, "tizhongval"));
        dto.setTizhongscreening(getScreeningResult(itemsispass, "体重、"));

        dto.setTzzsval(getStringValue(data, "tzzsval"));
        dto.setTzzsscore(getStringValue(data, "tzzsscore"));
        dto.setTzzsgrade(getStringValue(data, "tzzsgrade"));

        dto.setYaoweival(getStringValue(data, "yaoweival"));
        dto.setYaoweiscreening(getScreeningResult(itemsispass, "腰围、"));

        dto.setTunweival(getStringValue(data, "tunweival"));
        dto.setTunweiscreening(getScreeningResult(itemsispass, "臀围、"));

        dto.setTzlval(getStringValue(data, "tzlval"));
        dto.setTzlscore(getStringValue(data, "tzlscore"));
        dto.setTzlgrade(getStringValue(data, "tzlgrade"));
        dto.setTzlscreening(getScreeningResult(itemsispass, "体脂率、"));

        dto.setFhlval(getStringValue(data, "fhlval"));
        dto.setFhlscore(getStringValue(data, "fhlscore"));
        dto.setFhlgrade(getStringValue(data, "fhlgrade"));
        dto.setFhlscreening(getScreeningResult(itemsispass, "肺活量、"));

        dto.setGlcval(getStringValue(data, "glcval"));
        dto.setGlcscore(getStringValue(data, "glcscore"));
        dto.setGlcgrade(getStringValue(data, "glcgrade"));
        dto.setGlcscreening(getScreeningResult(itemsispass, "功率车、"));

        dto.setWolival(getStringValue(data, "wolival"));
        dto.setWoliscore(getStringValue(data, "woliscore"));
        dto.setWoligrade(getStringValue(data, "woligrade"));
        dto.setWoliscreening(getScreeningResult(itemsispass, "握力、"));

        dto.setZongtiaoval(getStringValue(data, "zongtiaoval"));
        dto.setZongtiaoscore(getStringValue(data, "zongtiaoscore"));
        dto.setZongtiaograde(getStringValue(data, "zongtiaograde"));
        dto.setZongtiaoscreening(getScreeningResult(itemsispass, "纵跳、"));

        // 根据性别设置不同的字段
        if ("男".equals(getStringValue(data, "sex"))) {
            dto.setFwcval(getStringValue(data, "fwcval"));
            dto.setFwcscore(getStringValue(data, "fwcscore"));
            dto.setFwcgrade(getStringValue(data, "fwcgrade"));
        } else {
            dto.setGwcval(getStringValue(data, "gwcval"));
            dto.setGwcscore(getStringValue(data, "gwcscore"));
            dto.setGwcgrade(getStringValue(data, "gwcgrade"));
        }
        dto.setFwgwscreening(getScreeningResult(itemsispass, "俯卧撑/跪卧撑、"));

        dto.setYfzywqzval(getStringValue(data, "yfzywqzval"));
        dto.setYfzywqzscore(getStringValue(data, "yfzywqzscore"));
        dto.setYfzywqzgrade(getStringValue(data, "yfzywqzgrade"));
        dto.setYfzywqzscreening(getScreeningResult(itemsispass, "1分钟仰卧起坐、"));

        dto.setZwtqqval(getStringValue(data, "zwtqqval"));
        dto.setZwtqqscore(getStringValue(data, "zwtqqscore"));
        dto.setZwtqqgrade(getStringValue(data, "zwtqqgrade"));
        dto.setZwtqqscreening(getScreeningResult(itemsispass, "坐位体前屈、"));

        dto.setBydjzlval(getStringValue(data, "bydjzlval"));
        dto.setBydjzlscore(getStringValue(data, "bydjzlscore"));
        dto.setBydjzlgrade(getStringValue(data, "bydjzlgrade"));
        dto.setBydjzlscreening(getScreeningResult(itemsispass, "闭眼单脚站立、"));

        dto.setXzfysval(getStringValue(data, "xzfysval"));
        dto.setXzfysscore(getStringValue(data, "xzfysscore"));
        dto.setXzfysgrade(getStringValue(data, "xzfysgrade"));
        dto.setXzfysscreening(getScreeningResult(itemsispass, "选择反应时、"));

        dto.setBeilival(getStringValue(data, "beilival"));
        dto.setBeiliscreening(getScreeningResult(itemsispass, "背力、"));

        dto.setYtbscreening(getScreeningResult(itemsispass, "腰臀比"));
        dto.setMycscreening(getScreeningResult(itemsispass, "脉压差"));
        dto.setSgtzscreeningCr(getScreeningResult(itemsispass, "身高与体重"));
        dto.setSgfhlscreening(getScreeningResult(itemsispass, "身高与肺活量"));
        dto.setSgwlscreening(getScreeningResult(itemsispass, "身高与握力"));
        dto.setSgblscreening(getScreeningResult(itemsispass, "身高与背力"));
        dto.setTzfhlscreening(getScreeningResult(itemsispass, "体重与肺活量"));
        dto.setTzwlscreening(getScreeningResult(itemsispass, "体重与握力"));
        dto.setTzblscreening(getScreeningResult(itemsispass, "体重与背力"));
        dto.setYwsgtzscreening(getScreeningResult(itemsispass, "腰围/身高与体重"));
        dto.setTwsgtzscreening(getScreeningResult(itemsispass, "臀围/身高与体重"));

        dto.setZongheScore(getStringValue(data, "zongheScore"));
        dto.setZongheGrade(getStringValue(data, "zongheGrade"));

        String ispassscreening = getStringValue(data, "ispassscreening");
        dto.setIspassscreening(ispassscreening.isEmpty() ? "" : "0".equals(ispassscreening) ? "不通过" : "通过");

        dto.setHeartrate(getStringValue(data, "heartrate"));
        dto.setAjmbscreening(getScreeningResult(itemsispass, "安静脉搏、"));

        dto.setDiastolicpressure(getStringValue(data, "diastolicpressure"));
        dto.setSystolicpressurescreening(getScreeningResult(itemsispass, "收缩压、"));

        dto.setSystolicpressure(getStringValue(data, "systolicpressure"));
        dto.setDiastolicpressurescreening(getScreeningResult(itemsispass, "舒张压、"));

        dto.setMetval(getStringValue(data, "metval"));
        dto.setAbsoluteval(getStringValue(data, "absoluteval"));
        dto.setRelativeval(getStringValue(data, "relativeval"));
        dto.setGlcHeartRate(getStringValue(data, "glcHeartRate"));
    }

    // 设置老年人特定字段
    private void setLaoRenFields(QuestionnaireDataDTO dto, Map<String, Object> data) {
        String itemsispass = getStringValue(data, "itemsispass");

        dto.setShengaoval(getStringValue(data, "shengaoval"));
        dto.setShengaoscreening(getScreeningResult(itemsispass, "身高、"));

        dto.setTizhongval(getStringValue(data, "tizhongval"));
        dto.setTizhongscreening(getScreeningResult(itemsispass, "体重、"));

        dto.setTzzsval(getStringValue(data, "tzzsval"));
        dto.setTzzsscore(getStringValue(data, "tzzsscore"));
        dto.setTzzsgrade(getStringValue(data, "tzzsgrade"));

        dto.setYaoweival(getStringValue(data, "yaoweival"));
        dto.setYaoweiscreening(getScreeningResult(itemsispass, "腰围、"));

        dto.setTunweival(getStringValue(data, "tunweival"));
        dto.setTunweiscreening(getScreeningResult(itemsispass, "臀围、"));

        dto.setTzlval(getStringValue(data, "tzlval"));
        dto.setTzlscore(getStringValue(data, "tzlscore"));
        dto.setTzlgrade(getStringValue(data, "tzlgrade"));
        dto.setTzlscreening(getScreeningResult(itemsispass, "体脂率、"));

        dto.setFhlval(getStringValue(data, "fhlval"));
        dto.setFhlscore(getStringValue(data, "fhlscore"));
        dto.setFhlgrade(getStringValue(data, "fhlgrade"));
        dto.setFhlscreening(getScreeningResult(itemsispass, "肺活量、"));

        dto.setLfzydgttzval(getStringValue(data, "lfzydgttzval"));
        dto.setLfzydgttyval(getStringValue(data, "lfzydgttyval"));
        dto.setLfzydgttval(getStringValue(data, "lfzydgttval"));
        dto.setLfzydgttscore(getStringValue(data, "lfzydgttscore"));
        dto.setLfzydgttgrade(getStringValue(data, "lfzydgttgrade"));
        dto.setLfzydgttscreening(getScreeningResult(itemsispass, "2分钟原地高抬腿、"));

        dto.setWolival(getStringValue(data, "wolival"));
        dto.setWoliscore(getStringValue(data, "woliscore"));
        dto.setWoligrade(getStringValue(data, "woligrade"));
        dto.setWoliscreening(getScreeningResult(itemsispass, "握力、"));

        dto.setZwtqqval(getStringValue(data, "zwtqqval"));
        dto.setZwtqqscore(getStringValue(data, "zwtqqscore"));
        dto.setZwtqqgrade(getStringValue(data, "zwtqqgrade"));
        dto.setZwtqqscreening(getScreeningResult(itemsispass, "坐位体前屈、"));

        dto.setBydjzlval(getStringValue(data, "bydjzlval"));
        dto.setBydjzlscore(getStringValue(data, "bydjzlscore"));
        dto.setBydjzlgrade(getStringValue(data, "bydjzlgrade"));
        dto.setBydjzlscreening(getScreeningResult(itemsispass, "闭眼单脚站立、"));

        dto.setXzfysval(getStringValue(data, "xzfysval"));
        dto.setXzfysscore(getStringValue(data, "xzfysscore"));
        dto.setXzfysgrade(getStringValue(data, "xzfysgrade"));
        dto.setXzfysscreening(getScreeningResult(itemsispass, "选择反应时、"));

        dto.setSsmzzval(getStringValue(data, "ssmzzval"));
        dto.setSsmzzscore(getStringValue(data, "ssmzzscore"));
        dto.setSsmzzgrade(getStringValue(data, "ssmzzgrade"));
        dto.setSsmzzscreening(getScreeningResult(itemsispass, "30秒坐站、"));

        dto.setYtbscreeningLr(getScreeningResult(itemsispass, "腰臀比"));
        dto.setMycscreeningLr(getScreeningResult(itemsispass, "脉压差"));
        dto.setSgtzscreeningLr(getScreeningResult(itemsispass, "身高与体重"));
        dto.setSgfhlscreeningLr(getScreeningResult(itemsispass, "身高与肺活量"));
        dto.setSgwlscreeningLr(getScreeningResult(itemsispass, "身高与握力"));
        dto.setTzfhlscreeningLr(getScreeningResult(itemsispass, "体重与肺活量"));
        dto.setTzwlscreeningLr(getScreeningResult(itemsispass, "体重与握力"));
        dto.setYwsgtzscreeningLr(getScreeningResult(itemsispass, "腰围/身高与体重"));
        dto.setTwsgtzscreeningLr(getScreeningResult(itemsispass, "臀围/身高与体重"));

        dto.setZongheScore(getStringValue(data, "zongheScore"));
        dto.setZongheGrade(getStringValue(data, "zongheGrade"));

        String ispassscreening = getStringValue(data, "ispassscreening");
        dto.setIspassscreening(ispassscreening.isEmpty() ? "" : "0".equals(ispassscreening) ? "不通过" : "通过");

        dto.setHeartrate(getStringValue(data, "heartrate"));
        dto.setAjmbscreening(getScreeningResult(itemsispass, "安静脉搏、"));

        dto.setDiastolicpressure(getStringValue(data, "diastolicpressure"));
        dto.setSystolicpressurescreening(getScreeningResult(itemsispass, "收缩压、"));

        dto.setSystolicpressure(getStringValue(data, "systolicpressure"));
        dto.setDiastolicpressurescreening(getScreeningResult(itemsispass, "舒张压、"));
    }

    // 设置通用字段
    private void setCommonFields(QuestionnaireDataDTO dto, Map<String, Object> data) {
        dto.setQztzShicezhi(getStringValue(data, "QZTZ_SHICEZHI"));
        dto.setTzflShicezhi(getStringValue(data, "TZFL_SHICEZHI"));
        dto.setStjrlShicezhi(getStringValue(data, "STJRL_SHICEZHI"));
        dto.setZszjrlShicezhi(getStringValue(data, "ZSZJRL_SHICEZHI"));
        dto.setYszjrlShicezhi(getStringValue(data, "YSZJRL_SHICEZHI"));
        dto.setQgjrlShicezhi(getStringValue(data, "QGJRL_SHICEZHI"));
        dto.setZxzjrlShicezhi(getStringValue(data, "ZXZJRL_SHICEZHI"));
        dto.setYxzjrlShicezhi(getStringValue(data, "YXZJRL_SHICEZHI"));
        dto.setQgzflShicezhi(getStringValue(data, "QGZFL_SHICEZHI"));
        dto.setZfljgFatW(getStringValue(data, "ZFLJG_FAT_W"));
        dto.setGps(getStringValue(data, "gps"));

        String questionTime = getStringValue(data, "questionTime");
        if (StringUtils.isNotEmpty(questionTime) && questionTime.length() >= 10) {
            dto.setQuestionTime(questionTime.substring(0, 10));
        }
    }

    // 设置问卷问题字段
    private void setQuestionnaireFields(QuestionnaireDataDTO dto, Map<String, Object> data,
                                        List<Map<String, Object>> paperList) {
        // 使用反射动态设置问卷问题字段
        for (int j = 0; j < paperList.size(); j++) {
            Map<String, Object> paper = paperList.get(j);
            String paperId = getStringValue(paper, "paperId");

            int n = calculateQuestionCount(paper);

            for (int k = 0; k < n; k++) {
                String fieldName = "question" + (j + 1) + "_" + (k + 1);
                String value = getQuestionValue(data, paperId, k, n);

                try {
                    Field field = QuestionnaireDataDTO.class.getDeclaredField(fieldName);
                    field.setAccessible(true);
                    field.set(dto, value);
                } catch (Exception e) {
                    log.warn("设置问卷字段{}失败", fieldName, e);
                }
            }

            if (n == 0) {
                String fieldName = "question" + (j + 1);
                String value = getStringValue(data, paperId);

                try {
                    Field field = QuestionnaireDataDTO.class.getDeclaredField(fieldName);
                    field.setAccessible(true);
                    field.set(dto, value);
                } catch (Exception e) {
                    log.warn("设置问卷字段{}失败", fieldName, e);
                }
            }
        }
    }

    // 设置个人信息字段
    private void setPersonalInfoFields(QuestionnaireDataDTO dto, Map<String, Object> data) throws Exception {
        dto.setWorkunit(CryptoUtils.decryptSymmetrically(Global.COLS_AES_SECRETKEY, null,
                getStringValue(data, "workunit"), CryptoUtils.Algorithm.Encryption.AES_ECB_PKCS5));
        dto.setNation(getStringValue(data, "nation"));
        dto.setPosition(getStringValue(data, "position"));
        dto.setOccupation(getStringValue(data, "occupation"));
        dto.setIndustry(getStringValue(data, "industry"));
        dto.setCulture(getStringValue(data, "culture"));
        dto.setAddress(CryptoUtils.decryptSymmetrically(Global.COLS_AES_SECRETKEY, null,
                getStringValue(data, "address"), CryptoUtils.Algorithm.Encryption.AES_ECB_PKCS5));
        dto.setPhone(CryptoUtils.decryptSymmetrically(Global.COLS_AES_SECRETKEY, null,
                getStringValue(data, "phone"), CryptoUtils.Algorithm.Encryption.AES_ECB_PKCS5));
    }

    // 计算问题数量
    private int calculateQuestionCount(Map<String, Object> paper) {
        int n = 0;
        String paperTitle = getStringValue(paper, "paperTitle");
        String choiceText = getStringValue(paper, "choice_text");
        String paperCss = getStringValue(paper, "paperCss");

        // 填空题题目中包含@
        if (paperTitle.contains("@")) {
            n = paperTitle.length() - paperTitle.replaceAll("@", "").length();
        }

        // 选择填空题选项中包含@
        if (StringUtils.isNotEmpty(choiceText) && choiceText.contains("@")) {
            n = choiceText.length() - choiceText.replaceAll("@", "").length() + 1;

            // 成年人11那个题型中，是填空题，但是题目中不包含@，在选项中包含@，所以列不需要加1
            if (StringUtils.isNotEmpty(paperCss) && "100".equals(paperCss)) {
                n = n - 1;
            }
        }

        return n;
    }

    // 获取问题值
    private String getQuestionValue(Map<String, Object> data, String paperId, int index, int total) {
        Object value = data.get(paperId);
        if (value == null) {
            return "";
        }

        String valueStr = value.toString();
        if (valueStr.contains(",")) {
            String[] values = valueStr.split(",");
            return index < values.length ? values[index].replaceAll("@", "\" \"") : "";
        } else if (total > 0 && index == 0) {
            return valueStr.replaceAll("@", "\" \"");
        } else {
            return "";
        }
    }

    // 获取筛查结果
    private String getScreeningResult(String itemsispass, String itemName) {
        if (StringUtils.isEmpty(itemsispass) || !itemsispass.contains(itemName)) {
            return "";
        } else {
            return "未通过";
        }
    }

    // 获取字符串值
    private String getStringValue(Map<String, Object> data, String key) {
        Object value = data.get(key);
        return value == null ? "" : value.toString();
    }



}
